﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aden.Util.Database;
using Aden.Util.Common;
using Aden.Model.SFEED;
using Aden.DAL.SOAccount;
using Aden.DAL.SalesOrder;
using System.Data;
using System.Runtime.InteropServices.WindowsRuntime;

namespace Aden.DAL.SFEED
{
    public class SFEEDFactory
    {

        public bool CheckWechatLoginUser(string wechatId)
        {
            try
            {
                string sql = "select top 1 1 from tblUser(nolock) where wechatId='{0}' ;";
                sql = string.Format(sql, wechatId);
                DataTable data = SqlServerHelper.GetDataTable(SqlServerHelper.sfeed(), sql);
                if (data == null || data.Rows.Count == 0) return false;
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public dynamic GetWechatMenuList(string wechatId)
        {
            //string sql = "select a3.UserID,a1.MenuNameCn,a1.MenuAction,a1.menuiconclassname,a1.menuiconimg," +
            //            "a3.SiteGUID,a4.Code,ISNULL(a4.readshippmentfromdb,0) readshippmentfromdb " +
            //            "from tblmenu (nolock) a1,tbladminmenu(nolock) a2, tblUser(nolock) a3, tblsite(nolock) a4 " +
            //            "where a1.menuguid = a2.menuguid and a2.userid = a3.UserID and a3.siteguid=a4.guid and " +
            //            "a1.menutype = 'Wechat' and a3.WechatId = '{0}' and (a1.siteneedwork = 0 or a4.NeedWork =1);";
            string sql = "select a3.UserID," +
                        "a3.SiteGUID,a4.Code " +
                        "from tblUser(nolock) a3, tblsite(nolock) a4 " +
                        "where a3.siteguid=a4.guid and " +
                        "a3.WechatId = '{0}' ;";
            sql = string.Format(sql, wechatId);
            DataTable data = SqlServerHelper.GetDataTable(SqlServerHelper.sfeed(), sql);

            if (data == null || data.Rows.Count == 0) throw new Exception("没有权限");

            return data.AsEnumerable().GroupBy(q => q.Field<string>("siteGUID")).Select(q => new
            {
                siteGuid = q.Key,
                costCenterCode = q.FirstOrDefault().Field<string>("code"),
                userID = q.FirstOrDefault().Field<int>("userID")
            }).ToList();
        }

        public List<SaleOrder> GetSOList(string maxOrderId, string siteGuid, string orderDate, string orderstatus, string orderCode, string language)
        {
            string filterDate = "";
            string filterSiteOC = string.Format(" a1.SiteGuid = '{0}' and a1.orderCode = '{1}' and ", siteGuid, orderCode);
            if (string.IsNullOrWhiteSpace(orderDate))
            {
                orderDate = DateTime.Now.AddYears(-10).ToString("yyyy-MM-dd");
                filterDate = string.Format(" convert(varchar(10),a1.RequiredDate,120)>='{0}' and ", DateTime.Parse(orderDate).ToString("yyyy-MM-dd"));
            }
            else if (!string.IsNullOrWhiteSpace(orderDate))
            {
                filterDate = string.Format(" convert(varchar(10),a1.RequiredDate,120)='{0}' and ", DateTime.Parse(orderDate).ToString("yyyy-MM-dd"));
            }

            if (string.IsNullOrWhiteSpace(orderCode))
            {
                filterSiteOC = string.Format(" a1.SiteGuid = '{0}' and ", siteGuid);
            }

            string sql = "select a1.orderid, " +
                 "a3.needWork,a1.WorkedDate,a1.WorkedUser,a1.guid orderguid,a1.ordercode,a2.userid, isnull(a2.UserName,a2.FirstName) UserName,a2.WechatID,a1.RequiredDate,a1.PaymentAmount," +
                 "case a1.paymentid when 10 then '支付宝' when 20 then '微信' when 30 then '员工卡' else '' end paymentmethod,a1.shippeddate,a1.comments," +
                 "case when a1.ShippedDate is null then '<input type=checkbox class=minimal >' else '' end as status, " +
                 "isnull(a1.PhoneNo,a2.Mobile) Mobile,isnull(a2.Department,'') Department,isnull(a2.Section,'') Section, a1.ShipCode, a1.ShipToAddr," +
                 "convert(varchar,dateadd(mi,timestep,endtime),120) DeliveryEndTime " +
                 "from tblSaleOrder(nolock) a1 join tbluser(nolock) a2 on a1.userid=a2.userid " +
                 "join tblSite(nolock) a3 on a3.GUID=a1.SiteGuid " +
                 "left join tblDeliveryTimes(nolock) a4 on a1.siteguid=a4.siteguid and " +
                 "convert(time(0),a1.requireddate,8) >= convert(time(0),a4.starttime,8) and " +
                 "convert(time(0),a1.requireddate,8) <= convert(time(0),dateadd(mi,timestep,endtime),8) " +
                 "where " + filterSiteOC + " {0}" +
                 "a1.IsDel=0 and a1.ispaid=1 {1} {2} " +
                 "order by a1.ordercode; ";
            string sqlstatus = "";
            switch (orderstatus)
            {
                case "ToBeWorked":
                    {
                        sqlstatus = " and a1.WorkedDate is null and a1.ShippedDate is null";
                        break;
                    }
                case "ToBeShipped":
                    {
                        sqlstatus = " and (a3.needWork=0 or a1.WorkedDate is not null) "
                            +"and a1.shippeddate is null";
                        break;
                    }
                case "Shipped":
                    {
                        sqlstatus = " and a1.shippeddate is not null";
                        break;
                    }
            }

            sql = string.Format(sql, filterDate, sqlstatus,
                string.IsNullOrWhiteSpace(maxOrderId) ? "" : " and a1.orderid " + maxOrderId);

            DataTable data = SqlServerHelper.GetDataTable(SqlServerHelper.sfeed(), sql);
            if (data == null || data.Rows.Count == 0) return null;

            int dataLength = data.Rows[data.Rows.Count - 1].Field<int>("OrderID");

            var list = data.AsEnumerable()
                .Select(dr =>
                    new SaleOrder()
                    {
                        HeadGUID = dr.Field<string>("OrderGuid").GetString().Trim(),
                        UserID = dr.Field<int>("userid"),
                        UserName = dr.Field<string>("UserName").GetString().Trim(),
                        OrderDate = dr.Field<DateTime>("RequiredDate").ToString("yyyy-MM-dd")
                    }).ToList();

            List<SaleLine> getSO = GetSO("", language).ToList();
            list = list.GroupJoin(getSO, a => a.HeadGUID, b => b.HeadGUID, (a, b) => new { a, b }).Select(q =>
            {
                return new SaleOrder()
                {
                    HeadGUID = q.a.HeadGUID,
                    Lines = q.b.ToList() == null ? null : q.b.ToList(),
                    UserID = q.a.UserID,
                    UserName = q.a.UserName,
                    OrderDate = q.a.OrderDate
                };
            }).ToList();

            return list;
        }

        public List<SaleLine> GetSO(string orderGuid, string language)
        {
            string filter = "";
            if (!string.IsNullOrWhiteSpace(orderGuid)) filter = string.Format(" a1.SOGUID = '{0}' and ", orderGuid);
            string sql = "select a1.SOGUID, a2.ItemCode,a2.ItemNameZHCN as ItemName_CN,a2.ItemNameENUS as ItemName_EN," +
                "a4.ClassName,a1.price,a1.qty,case '{0}' when 'zh' then isnull(a3.NameCn,'pc') else isnull(a3.nameen,'pc') end unitname " +
                "from tblsaleorderitem (nolock) a1,tblitem (nolock) a2 left join tbluom (nolock) a3 on a2.SaleUOMGUID=a3.GUID " +
                "left join tblItemClass (nolock) a4 on a4.GUID=a2.ClassGUID " +
                "where {1} a1.itemguid=a2.guid order by a1.id;";
            sql = string.Format(sql, language, filter);
            DataTable data = SqlServerHelper.GetDataTable(SqlServerHelper.sfeed(), sql);
            if (data == null || data.Rows.Count == 0) return null;
           
            var lines = data.AsEnumerable().Select(dr => new SaleLine()
            {
                HeadGUID = dr.Field<string>("SOGUID").GetString().Trim(),
                ItemCode = dr.Field<string>("ItemCode").GetString().Trim(),
                ItemName_CN = dr.Field<string>("ItemName_CN").GetString().Trim(),
                ItemName_EN = dr.Field<string>("ItemName_EN").GetString().Trim(),
                Qty = dr.Field<int>("Qty")
            }).ToList();
            return lines;
        }

        public int UpdateSOStatus(IList<SaleOrder> headGuids, string orderstatus, string userID)
        {
            try
            {
                if (headGuids == null || headGuids.Count == 0) throw new Exception("No Data to be processed");
                headGuids = headGuids.Where(q => !string.IsNullOrWhiteSpace(q.HeadGUID)).ToList();
                if (!headGuids.Any()) throw new Exception("No Data to be processed");

                string guids = string.Join("','", headGuids.Select(q => q.HeadGUID).ToArray());
                string createTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");

                string sql = "update [dbo].[tblsaleorder] set {0}='" + userID + "',{1}='" + createTime + "' {2} "
                    + "where guid in ('" + guids + "') and {0} is null and {1} is null";

                string sqltbl = "select OrderCode,CASE isnull(WorkedUser, '') when '' then '' else '订单已加工' End as WorkStatus, " +
                            " CASE isnull(ShippedUser, '') when '' then '' else '订单已发货' End as ShipStatus  from [dbo].[tblsaleorder]  " +
                            " where guid in ('" + guids + "') ;";
                switch (orderstatus)
                {
                    case "ToBeWorked":  // "Worked":
                        {
                            sql = string.Format(sql, "WorkedUser", "WorkedDate", "");
                            break;
                        }
                    case "ToBeShipped": //"Shipped":
                        {
                            sql = string.Format(sql, "ShippedUser", "ShippedDate",
                                ",Status='10' ");
                            break;
                        }
                }

                int r = SqlServerHelper.Execute( SqlServerHelper.sfeed() ,sql);
                if (r < headGuids.Count)
                {
                    DataTable dt = SqlServerHelper.GetDataTable(SqlServerHelper.sfeed(), sqltbl);
                    if(dt == null || dt.Rows.Count == 0)
                        throw new Exception("更新错误: 无效订单，请重新输入订单号");
                    var row = dt.Rows[0];
                    if (!string.IsNullOrWhiteSpace(row["ShipStatus"].ToString()))
                    {
                        string errorMsg = string.Format("更新错误: 订单:{0} 已发货，请重新输入订单号", row["OrderCode"].ToString());
                        throw new Exception(errorMsg);
                    }
                    if (!string.IsNullOrWhiteSpace(row["WorkStatus"].ToString()))
                    {
                        string errorMsg = string.Format("更新错误: 订单:{0} 已加工，请重新输入订单号", row["OrderCode"].ToString());
                        throw new Exception(errorMsg);
                    }
                }
                return r;
            }
            catch (Exception e) {
                throw e; 
            }
        }
    }
}
